# coding=utf-8
# 代码文件：Academicplatform/com/dao/request_dao.py

"""请求管理 DAO"""
import pymysql
from com.dao.base_dao import BaseDao


class RequestDao(BaseDao):
    def __init__(self):
        super().__init__()

    def findall(self):
        """查询所有请求"""

        requests = []
        try:
            # 创建游标对象
            with self.conn.cursor() as cursor:
                # 执行 SQL 操作
                sql = 'select requestid,userid,fileid,requestdate,status,points from requests'
                cursor.execute(sql)
                # 提取结果集
                result_set = cursor.fetchall()

                for row in result_set:
                    request = {}
                    request['requestid'] = row[0]
                    request['userid'] = row[1]
                    request['fileid'] = row[2]
                    request['requestdate'] = row[3]
                    request['status'] = row[4]
                    request['points'] = row[5]
                    requests.append(request)
                # with 代码块结束，关闭游标

        finally:
            # 关闭数据连接
            self.close()

        return requests

    def create(self, request):
        """创建请求，插入到数据库"""

        try:
            # 创建游标对象
            with self.conn.cursor() as cursor:
                # 执行 SQL 操作
                sql = 'insert into requests (requestid,userid,fileid,requestdate,status,points) ' \
                        'values (%s,%s,%s,%s,%s,%s)'
                affectedcount = cursor.execute(sql, request)
                print('成功插入{0}条数据'.format(affectedcount))
                # 提交数据库事务
                self.conn.commit()
                # with 代码块结束，关闭游标
        except pymysql.DatabaseError as e:
            # 回滚数据库事务
            self.conn.rollback()
            print(e)

        finally:
            # 关闭数据连接
            self.close()
